package org.compiere.process;

import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.adempiere.exceptions.AdempiereException;
import org.compiere.apps.ADialog;
import org.compiere.model.MAttachmentEntry;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.X_HR_Department;
import org.joda.time.DateTime;

import extend.org.compiere.hrm.ExcelCell;
import extend.org.compiere.utils.Util;

public class SummaryVacanciesPrint extends SvrProcess {
	
	/** Current context		*/
	private Properties m_ctx;
	/**	 */
	private ProcessInfo pi;
	/** */
	private int HR_Department_ID = 0;
	/** */
	private int HR_Block_ID = 0;
	/** */
	private String path = "";
	/** */
	private String isManagement = "N";
	/** */
	X_HR_Department department = null;
	/** */
	X_HR_Department block = null;
	/** CURRENT DATE */
	Timestamp dateFrom = new Timestamp(new DateTime().getMillis());
	SimpleDateFormat dateFormat = new SimpleDateFormat("ddMMyyyy");
	 
	/**	Static Logger	*/
	private static CLogger	s_log	= CLogger.getCLogger (SummaryVacanciesPrint.class);

	@Override
	protected void prepare() {
		
		log.info("Report for summary Vacancies");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
	
		ProcessInfoParameter[] para = getParameter();
		for (int i = 0; i < para.length; i++)	{
			String name = para[i].getParameterName();
			if (name == null); 
				//
			else if (name.compareTo("HR_Block_ID") == 0 && para[i].getParameter() != null)
				HR_Block_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.compareTo("HR_Department_ID") == 0 && para[i].getParameter() != null)
				HR_Department_ID = new BigDecimal(Integer.parseInt(para[i].getParameter().toString())).intValue();
			else if (name.toLowerCase().endsWith("directory"))
				path = (String)para[i].getParameter();
			else if (name.compareTo("isManagement") == 0)
				isManagement = (String) para[i].getParameter();
			else
			{
				log.log(Level.INFO, "Unknown Parameter: " + name);
			}
		}
		
		if(path.isEmpty())
			path = Util.localFilePath;
		else
			path = path + System.getProperty("file.separator");
		
		department = new X_HR_Department(getCtx(), HR_Department_ID, null);
		block = new X_HR_Department(getCtx(), HR_Block_ID, null);

	}

	@Override
	protected String doIt() throws Exception {
		
		// Types of cells for
		Label label;
		Number number;
		// There are checks for a specified pattern
		String templateName = "Сводка_по_вакансиям_по_состоянию_на_" + dateFormat.format(dateFrom);
		
		//
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, templateName);
		if(entry == null)
			throw new AdempiereException(Msg.translate(m_ctx, "NotFoundTemplate") + " " + templateName);
		
		// We define a path to generate
		String fileExtension = entry.getName().substring(entry.getName().lastIndexOf("."),entry.getName().length());
		StringBuffer fullPath = new StringBuffer(path);		
		fullPath.append(templateName).append(fileExtension);
		String sampleTemp = Util.localFilePath + "AdempiereTemp";
		
		// 
		File templateCopy = new File(fullPath.toString());
		File sampleCopy = new File(sampleTemp);
		File temporaryFile = entry.getFile(Util.localFilePath + templateName);
		File temporarySample = entry.getFile(Util.localFilePath + templateName);
		Workbook tableBook = null;
		WritableWorkbook copy = null;
		WritableSheet sheet = null;
		// for sample
		Workbook sample = null;
		WritableWorkbook samCopy = null;
		WritableSheet sheet_1 = null;
		try {
			tableBook = Workbook.getWorkbook(temporaryFile);
			copy = Workbook.createWorkbook(templateCopy, tableBook);
			sheet = copy.getSheet(0);
			// for sample
			sample = Workbook.getWorkbook(temporarySample);
			samCopy = Workbook.createWorkbook(sampleCopy, sample);
			sheet_1 = samCopy.getSheet(0);
			samCopy.write();
		} catch (Exception e) {
			ADialog.error(999, null, "Error tableBook. " + e.getMessage());
		    return "Error tableBook.";
		}

		ExcelCell cellStart =  Util.getCellStart(tableBook,">>");
		
       // Standart style cell
       WritableCellFormat borderStyle = new WritableCellFormat();
       borderStyle.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       borderStyle.setAlignment(Alignment.CENTRE);
       borderStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       borderStyle.setWrap(true);
       // Vacation style cell
       WritableFont fontDepartment = new WritableFont (WritableFont.TIMES, 12, WritableFont.BOLD);
       WritableCellFormat cellDepartment = new WritableCellFormat(fontDepartment);
       cellDepartment.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       cellDepartment.setAlignment(Alignment.CENTRE);
       cellDepartment.setVerticalAlignment(VerticalAlignment.CENTRE);
       cellDepartment.setBackground(Colour.TAN);
       // Title style cell
       WritableFont fontTitle = new WritableFont (WritableFont.TIMES, 14, WritableFont.BOLD);
       WritableCellFormat titleStyle = new WritableCellFormat(fontTitle);
       titleStyle.setAlignment(Alignment.CENTRE);
       titleStyle.setVerticalAlignment(VerticalAlignment.CENTRE);
       // Vacancy style cell
       WritableCellFormat minusCellVacancy = new WritableCellFormat();
       minusCellVacancy.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
       minusCellVacancy.setAlignment(Alignment.CENTRE);
       minusCellVacancy.setVerticalAlignment(VerticalAlignment.CENTRE);
       minusCellVacancy.setBackground(Colour.LIGHT_ORANGE);
       
	    // header report
	    WritableCell readCell = sheet.getWritableCell(1, 1);
		CellFormat readFormat = readCell.getCellFormat();
		label = new Label(1, 1, sheet.getCell(1, 1).getContents() + Util.getOrgInfo().getDescription(), readFormat);
		sheet.addCell(label);
       
       String managment = "";
       if(isManagement.equals("Y"))
    	   managment = "По управленческому составу ";
       
       // set title
       if (HR_Department_ID != 0){
    	   label = new Label(1, 2, managment + department.getName(), titleStyle);
		}else if(HR_Block_ID !=0){
			label = new Label(1, 2, managment + "Блока " + block.getName(), titleStyle);
		}else
			label = new Label(1, 2, managment, titleStyle);

       sheet.addCell(label);
	
       SummaryVacancyValue[] values = getValues();
       int col = cellStart.getC() + 1; // offset by the number of first columns
       int row = cellStart.getR();
       int rowC = 0;
       double sumVacancy = 0;
       int n = 0;
       int num = 1;
       int dId = 0; // Departmnet ID
       int jId = 0; // Job ID
       int dep = 0; int jo = 0; int flag = 0;
       for(int j = row; j < (row + values.length); j++){
    	   if(n == 0)
    		   jId = values[n].getJob_id();
    	   
    	   if(dId != values[n].getDepartment_id()){
	    	   dId = values[n].getDepartment_id();
	    	   sheet.insertRow(j+dep);
	    	   sheet.mergeCells(1, j+dep, 9, j+dep);
	    	   sheet.setRowView(j+dep, 750);
	    	   label = new Label(1, j+dep, values[n].getDepartment(), cellDepartment); 
		       sheet.addCell(label);
	    	   dep++;
    	   }
    	   
    	   sheet.setRowView(j+dep, 820);
    	   
    	   //numbers line
    	   label = new Label(col-1, j+dep, String.valueOf(num),borderStyle);
    	   sheet.addCell(label);
    	   //vacancy
    	   if(values[n].getVacancy().signum() >= 0)
    		   number = new Number(col, j+dep, values[n].getVacancy().doubleValue(),borderStyle);
    	   else
    		   number = new Number(col, j+dep, values[n].getVacancy().doubleValue(),minusCellVacancy);
    	   sheet.addCell(number);
    	   //job name
    	   label = new Label(col+1, j+dep, values[n].getJob(),borderStyle);
    	   sheet.addCell(label);
    	   // name BPartner
    	   label = new Label(col+2, j+dep, values[n].getName(),borderStyle);
    	   sheet.addCell(label);
    	   // wage rate
    	   label = new Label(col+3, j+dep, values[n].getWageRate(),borderStyle);
    	   sheet.addCell(label);
    	   // date contract
    	   label = new Label(col+4, j+dep, values[n].getDateContract(),borderStyle);
    	   sheet.addCell(label);
    	   // date to
    	   label = new Label(col+5, j+dep, values[n].getDateTo(),borderStyle);
    	   sheet.addCell(label);
    	   // date trial
    	   label = new Label(col+6, j+dep, values[n].getDateTrial(),borderStyle);
    	   sheet.addCell(label);
    	   // description
    	   label = new Label(col+7, j+dep, "",borderStyle);
    	   sheet.addCell(label);
    	   
    	   // combine the match
    	   if(jId == values[n].getJob_id())
    		   if(flag == 0){
    			   num++;
    			   sumVacancy = sumVacancy +  values[n].getVacancy().doubleValue();
    			   flag = j+dep;
    		   }
	    		else
	    			jo++;
    	   else{
    		   if(flag != 0){
    			   num++;
    			   sumVacancy = sumVacancy +  values[n].getVacancy().doubleValue();
    			   sheet.mergeCells(3, flag, 3, flag+jo);
    			   sheet.mergeCells(2, flag, 2, flag+jo);
    			   sheet.mergeCells(1, flag, 1, flag+jo);
    		   }
    		   jo = 0;
    		   flag = j+dep;
    	   }
    	   
    	   rowC = j+dep;
    	   
    	   jId = values[n].getJob_id();
    	   n++;
       }
       
       // last cell
       sheet.mergeCells(2, flag, 2, flag+jo);
       sheet.mergeCells(3, flag, 3, flag+jo);
       
       rowC+=3;
       sheet.mergeCells(col-1, rowC, col+1, rowC);
       sheet.setRowView(rowC, 700);
	   // Total
	   label = new Label(col-1, rowC, "ВСЕГО ВАКАНСИЙ", cellDepartment); 
	   sheet.addCell(label);
	   // Sum actual
	   	number = new Number(col+2, rowC, sumVacancy, borderStyle); 
	   	sheet.addCell(number); 
       
		copy.write();
	    copy.close();
	    tableBook.close();
	    temporaryFile.delete();
	       
		// Open the file with the application associated
		String fileName = fullPath.toString();
		Util.launchFile(new File(fileName));
		
		return Msg.translate(m_ctx, "Success");
	}
	
	private SummaryVacancyValue[] getValues(){
		
		SummaryVacancyValue values = null;
		List<SummaryVacancyValue> list = new ArrayList<SummaryVacancyValue>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;		
				
		try
		{
			pstmt = DB.prepareStatement(getSql(),null);

			if (HR_Department_ID != 0)
				pstmt.setInt(1, HR_Department_ID);
			else if(HR_Block_ID !=0)
				pstmt.setInt(1, HR_Block_ID);

			rs = pstmt.executeQuery();	
					
			while (rs.next ())
			{
				values = new SummaryVacancyValue();
				values.setDepartment_id(rs.getInt(1));
				values.setDepartment(rs.getString(2));
				values.setJob_id(rs.getInt(3));
				values.setJob(rs.getString(4));
				values.setVacancy(rs.getBigDecimal(5));
				values.setWageRate(rs.getString(6));
				values.setB_partner_id(rs.getInt(7));
				values.setName(rs.getString(8));
				values.setDateContract(rs.getString(9));
				values.setDateTrial(rs.getString(10));
				values.setDateTo(rs.getString(11));
				list.add(values);
				values=null;
			}
		}
		catch (SQLException e)
		{
			s_log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return list.toArray(new SummaryVacancyValue[list.size()]);
	}
	
	private String getSql(){
		
		String sql_ =  "SELECT 	T.HR_DEPARTMENT_ID AS DEPARTMENT_ID, \n"
						+" HD.NAME AS DEPARTMENT, \n"						
						+" T.HR_JOB_ID AS JOB_ID, \n"
						+" T.NAME AS JOB, \n"
						+" T.HRM_VACANCY AS VACANCY,\n"
						+" CASE WHEN WR.NAME IS NOT NULL THEN WR.NAME ELSE WR1.NAME END AS WAGERATE, \n"
						+" R.C_BPARTNER_ID AS BPARTNER_ID, \n"
						+" CASE WHEN (BP.FIRSTNAME IS NOT NULL AND BP.LASTNAME IS NOT NULL) THEN BP.LASTNAME||' '||BP.FIRSTNAME||' '||BP.MIDDLENAME ELSE BP.NAME END AS NAME,\n"
						+" COALESCE(TO_CHAR(HC.VALIDFROM,'DD.MM.YYYY'), (SELECT TO_CHAR(MIN(LHH.STARTDATE),'DD.MM.YYYY') FROM HRM_LIMITHOLIDAY LHH WHERE LHH.C_BPARTNER_ID=R.C_BPARTNER_ID)) AS DATECONTRACT,\n" 
						+" CASE WHEN HC.HRM_TRIALPERIOD_ID IS NOT NULL THEN \n"
						+" CASE WHEN HC.HRM_TRIALPERIOD_ID=1 THEN TO_CHAR(HC.VALIDFROM + INTERVAL '1 MONTH', 'DD.MM.YYYY') ELSE \n"
						+" CASE WHEN HC.HRM_TRIALPERIOD_ID=2 THEN TO_CHAR(HC.VALIDFROM + INTERVAL '2 MONTH', 'DD.MM.YYYY')  ELSE \n"
						+" CASE WHEN HC.HRM_TRIALPERIOD_ID=3 THEN TO_CHAR(HC.VALIDFROM + INTERVAL '3 MONTH', 'DD.MM.YYYY') END END END \n" 
						+" END AS DATETRIAL, \n"
						+" CASE WHEN HC.VALIDTO IS NOT NULL THEN TO_CHAR(HC.VALIDTO,'DD.MM.YYYY') ELSE TO_CHAR(HCL.VALIDTO,'DD.MM.YYYY') END AS DATETO\n" 
					+" FROM HR_JOB T \n"
					+" JOIN HR_DEPARTMENT HD ON HD.HR_DEPARTMENT_ID=T.HR_DEPARTMENT_ID AND HD.ISACTIVE='Y' \n"
					+" LEFT JOIN HR_EMPLOYEE R ON R.HR_JOB_ID=T.HR_JOB_ID AND R.ISACTIVE='Y' \n"
					+" AND R.ENDDATE = (SELECT MAX(TT.ENDDATE) FROM HR_EMPLOYEE TT WHERE TT.C_BPARTNER_ID=R.C_BPARTNER_ID) AND R.ENDDATE >= "+DB.TO_DATE(dateFrom, true)+" \n"
					+" LEFT JOIN C_BPARTNER BP ON BP.C_BPARTNER_ID=R.C_BPARTNER_ID\n"
					+" LEFT JOIN HR_CONTRACT HC ON HC.C_BPARTNER_ID=R.C_BPARTNER_ID AND HC.HRM_CONTRACTTYPE='1' AND HC.ISACTIVE='Y'\n"
					+" LEFT JOIN HR_CONTRACT_LINE HCL ON HCL.C_BPARTNER_ID=R.C_BPARTNER_ID AND HCL.ISACTIVE='Y'\n"
					+" LEFT JOIN HRM_WAGERATE WR ON WR.HRM_WAGERATE_ID=HC.HRM_WAGERATE_ID\n"
					+" LEFT JOIN HRM_WAGERATE WR1 ON WR1.HRM_WAGERATE_ID=HCL.HRM_WAGERATE_ID\n";
		
		StringBuffer sql = new StringBuffer(sql_);
		StringBuffer where = new StringBuffer(" WHERE T.ISACTIVE='Y' \n");
		
		if (HR_Department_ID != 0){
			sql.append(" AND r.hr_department_id=?  \n");
		}else if(HR_Block_ID !=0){
			sql.append(" AND r.hr_department_id IN (SELECT node_id FROM ad_treenodedep WHERE parent_id=?)  \n");
		}else
			where.append(" AND 1=1  \n");
		
		if(isManagement.equals("Y"))
			where.append(" AND T.ISMANAGEMENT='Y'  \n");
		else
			where.append(" AND T.ISMANAGEMENT='N'  \n");
		
		sql.append(where.toString());
		sql.append(" GROUP BY HD.NAME, HD.DESCRIPTION, \n"
				+" T.HR_JOB_ID, R.C_BPARTNER_ID, \n"
				+" BP.NAME, BP.LASTNAME, \n"
				+" BP.FIRSTNAME, BP.MIDDLENAME, \n"
				+" WR.NAME,WR1.NAME, HC.HRM_TRIALPERIOD_ID, \n"
				+" HC.VALIDFROM,HC.VALIDTO,HCL.VALIDTO \n"
		+" ORDER BY HD.DESCRIPTION::NUMERIC, T.HR_DEPARTMENT_ID, T.HR_JOB_ID");
		
		return sql.toString();
		
	}
	
	public class SummaryVacancyValue{
		
		private int department_id;
		private String department;
		private int job_id;
		private String job;
		private BigDecimal vacancy;
		private String wageRate;
		private int b_partner_id;
		private String name;
		private String dateContract;
		private String dateTrial;
		private String dateTo;
		public int getDepartment_id() {
			return department_id;
		}
		public void setDepartment_id(int department_id) {
			this.department_id = department_id;
		}
		public String getDepartment() {
			return department;
		}
		public void setDepartment(String department) {
			this.department = department;
		}
		public int getJob_id() {
			return job_id;
		}
		public void setJob_id(int job_id) {
			this.job_id = job_id;
		}
		public String getJob() {
			return job;
		}
		public void setJob(String job) {
			this.job = job;
		}
		public BigDecimal getVacancy() {
			return vacancy;
		}
		public void setVacancy(BigDecimal vacancy) {
			this.vacancy = vacancy;
		}
		public String getWageRate() {
			return wageRate;
		}
		public void setWageRate(String wageRate) {
			this.wageRate = wageRate;
		}
		public int getB_partner_id() {
			return b_partner_id;
		}
		public void setB_partner_id(int b_partner_id) {
			this.b_partner_id = b_partner_id;
		}
		public String getName() {
			return name;
		}
		public void setName(String name) {
			this.name = name;
		}
		public String getDateContract() {
			return dateContract;
		}
		public void setDateContract(String dateContract) {
			this.dateContract = dateContract;
		}
		public String getDateTrial() {
			return dateTrial;
		}
		public void setDateTrial(String dateTrial) {
			this.dateTrial = dateTrial;
		}
		public String getDateTo() {
			return dateTo;
		}
		public void setDateTo(String dateTo) {
			this.dateTo = dateTo;
		}
		
	}

}
